from pymysql import *
from db import conn_db_conf as conn
import pandas as pd
#显示所有列
pd.set_option('display.max_columns', None)
#显示所有行
pd.set_option('display.max_rows', None)
#设置value的显示长度为100，默认为50
pd.set_option('max_colwidth', 100)


# Y1DTTM,Y1DTTP,Y1MP,Y1SQII,Y1SQID,Y1FQII,Y1FQID,Y1FQPK,Y1ISTY,Y1SECP,Y1ACDE,Y1QISI,Y1BOXI,Y1BOXF,Y1QISF,Y1USED,Y1USET,Y1SETI,Y1XISS,Y1INPN,Y1STKN,Y1XLOC,Y1IT,Y1SD,Y1SP,Y1PS,Y1ITOH,Y1PCK,Y1PCKQ,Y1NOOV,Y1XTIV,Y1CON,Y1PLT,Y1TRD,Y1TRT,Y1FLAG,Y1XOPA,Y1XOPB,Y1XOPC,Y1XOPD,Y1XOPE,Y1XOPF,Y1XOPG,Y1RSV1,Y1RSV2,Y1RSV3
class OCR_DATA():
    def __init__(self, user, password, database, port=3306, host='localhost'):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.conn = self.create_conn()  # 光标
        if not self.conn:
            print("连接数据库异常")
            exit()
        self.cursor = self.conn.cursor(cursor=cursors.DictCursor)  # 查询结果以字典方式返回

    def __del__(self):
        """程序退出 释放资源"""
        try:
            self.conn.close()
            self.cursor.close()
        except:
            pass

    # 创建与数据库连接
    def create_conn(self):
        try:
            # cursorclass=cursors.DictCursor  字典数据返回
            conn = connect(host=self.host, port=self.port, user=self.user, password=self.password, database=self.database, charset='utf8', cursorclass=cursors.DictCursor)
            return conn
        except:
            return None
    #
    # ['Y1DTTM', 'Y1DTTP', 'Y1MP', 'Y1SQII', 'Y1SQID', 'Y1FQII', 'Y1FQID', 'Y1FQPK', 'Y1ISTY', 'Y1SECP', 'Y1ACDE',
    #  'Y1QISI', 'Y1BOXI', 'Y1BOXF', 'Y1QISF', 'Y1USED', 'Y1USET', 'Y1SETI', 'Y1XISS', 'Y1INPN', 'Y1STKN', 'Y1XLOC',
    #  'Y1IT', 'Y1SD', 'Y1SP', 'Y1PS', 'Y1ITOH', 'Y1PCK', 'Y1PCKQ', 'Y1NOOV', 'Y1XTIV', 'Y1CON', 'Y1PLT', 'Y1TRD',
    #  'Y1TRT', 'Y1FLAG', 'Y1XOPA', 'Y1XOPB', 'Y1XOPC', 'Y1XOPD', 'Y1XOPE', 'Y1XOPF', 'Y1XOPG', 'Y1RSV1', 'Y1RSV2',
    #  'Y1RSV3']
    def read_excel(self):
        df = pd.read_excel(r'E:\OCR_RMC\ORC_BAR4.0\db\123.xlsx')
        df.fillna(" ", inplace=True)
        db_title = list(df.columns)
        aa = []
        values_ = df.values
        for i in values_:
           aa.append(tuple(list(i)))

        for index, value in enumerate(db_title):
            value = value.replace('\n','').replace('\r','')
            db_title[index] = value.strip()

        # 创建最终返回的空字典
        df_dict = {}
        # 读取Excel文件
        #    df = pd.read_excel(path)

        # 替换Excel表格内的空单元格，否则在下一步处理中将会报错

        # print(d'f.index.values)
        # print(db_title)
        # print(df.loc[0].index)
        insert_data = []
        for i in df.index.values:
            db_value = list(df.loc[i].values)
            data_dict = {}
            for index, key in enumerate(db_title):
                data_dict[key] = db_value[index]
            insert_data.append(data_dict)
        # print(insert_data)
        return insert_data, aa
    #     return df_dict

    # 验证多条数据以字典嵌套插入异常, 只能单条插入
    def insert_Group_dict(self, insert_dataDict=None, db_name='ocr'):
        """ 字典方式插入数据 多组数据插入  防SQL注入插入数据
            insert_dataDict: 插入的数据, 单条数据为字典, 多条插入为列表套字典,
            serial_number: 唯一码,
            db_name: 数据表名
        """
        # 检查数据是否有重复的
        # try:
        #     de_weight = self.item_getdata(db_name=db_name, serial_number=serial_number)
        # except:
        #     de_weight = ()
        de_weight = True
        if de_weight:   # 如有重复数据 停止插入
            # 这里db_name和insert_dataDict，其中insert_dataDict是一个字典，写插入数据库的代码
            # 获取插入数据的头部数据 KEYS  列名
            # 传进来参数为字典, 当insert_dataDict为字典时 为单个数据插入
            if isinstance(insert_dataDict, dict):
                print("字典插入单条数据")
                cols = ", ".join('`{}`'.format(k) for k in insert_dataDict.keys())
                # 获取值
                val_cols = ', '.join('%({})s'.format(k) for k in insert_dataDict.keys())
                a = [insert_dataDict]  # 拼出要插入的内容
            # 数据为列表嵌套字典时为多字典/多条数据插入
            elif isinstance(insert_dataDict, list):
                print("列表嵌套字典插入数据!")
                cols = ", ".join('`{}`'.format(k) for k in insert_dataDict[0].keys())
                # 获取值
                val_cols = ', '.join('%({})s'.format(k) for k in insert_dataDict[0].keys())
                a = insert_dataDict
            # 获取插入SQL 命令 insert_dataDict
            sql = "insert into {}(%s) values(%s)".format(db_name)
            res_sql = sql % (cols, val_cols)
            # print(res_sql)
            try:
                self.cursor.executemany(res_sql, a)  # 将字典列表a传入
                self.cursor.connection.commit()
                # self.conn.commit()
            except Exception as e:
                print("字典数据插入异常: ", e)
                self.conn.rollback()   # 回滚 插入数据无效
        else:
            print("数据重复插入")

    # Y1DTTM,Y1DTTP,Y1MP,Y1SQII,Y1SQID,Y1FQII,Y1FQID,Y1FQPK,Y1ISTY,Y1SECP,Y1ACDE,Y1QISI,Y1BOXI,Y1BOXF,Y1QISF,Y1USED,Y1USET,Y1SETI,Y1XISS,Y1INPN,Y1STKN,Y1XLOC,Y1IT,Y1SD,Y1SP,Y1PS,Y1ITOH,Y1PCK,Y1PCKQ,Y1NOOV,Y1XTIV,Y1CON,Y1PLT,Y1TRD,Y1TRT,Y1FLAG,Y1XOPA,Y1XOPB,Y1XOPC,Y1XOPD,Y1XOPE,Y1XOPF,Y1XOPG,Y1RSV1,Y1RSV2,Y1RSV3
    def insert_Group_List(self, insert_dataList, db_name='ocr'):
        """ 插入多组数据 列表方式插入
            insert_dataList: 插入的数据 列表套元组, 多条为多个元组
            db_name: 数据表名
        """
        val = '%s, ' * (len(insert_dataList[0]) - 1) + '%s'
        sql = f'insert into {db_name} values ({val})'
        try:
            # self.cursor.execute(sql, insert_dataList)
            # 多条数据插入
            # 插入数据格式  [(0, '99yyy', 779, 987656787890, ''), (0, 'QWE8890', 990, 987654456789, '')]
            self.cursor.executemany(sql, insert_dataList)
            self.conn.commit()
        except Exception as e:
            print("输入插入有异常", e)
            self.conn.rollback()  # 异常回转, 所有数据插入无效

    def select_OCR(self, Y1DTTM=None):
        if Y1DTTM:
            sql = '''select * from ocr where Y1DTTM={}'''.format(Y1DTTM)
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            print(result)

    def update_ocr(self, item, value, Y1DTTM):
        try:
            sql = """ update ocr set {}={} where Y1DTTM="{}" """.format(item, value, Y1DTTM)
            self.cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            print(e)


if __name__=='__main__':
    a = {'Y1DTTM': '202106020815005340001', 'Y1DTTP': 4, 'Y1MP': 'EA', 'Y1SQII': 9909004, 'Y1SQID': 1, 'Y1FQII': 550553,
         'Y1FQID': 307, 'Y1FQPK': 1, 'Y1ISTY': 'VM1', 'Y1SECP': 1113, 'Y1ACDE': '12#4F', 'Y1QISI': 83, 'Y1BOXI': 2000,
         'Y1BOXF': 2000, 'Y1QISF': 800, 'Y1USED': 20210602, 'Y1USET': 84500, 'Y1SETI': 'AAUTO', 'Y1XISS': 'AAUTO',
         'Y1INPN': 1, 'Y1STKN': 'OC', 'Y1XLOC': 'TC00001', 'Y1IT': 'D2625368', 'Y1SD': 'A', 'Y1SP': ' ', 'Y1PS': 'P',
         'Y1ITOH': 'D2625368A-+-P', 'Y1PCK': 11000001.0, 'Y1PCKQ': 400.0, 'Y1NOOV': ' ', 'Y1XTIV': 1.0,
         'Y1CON': 'TRUCK1',
         'Y1PLT': 'TEST0001', 'Y1TRD': 20210602, 'Y1TRT': 81500, 'Y1FLAG': ' ', 'Y1XOPA': ' ', 'Y1XOPB': ' ',
         'Y1XOPC': ' ',
         'Y1XOPD': ' ', 'Y1XOPE': '*', 'Y1XOPF': ' ', 'Y1XOPG': ' ', 'Y1RSV1': ' ', 'Y1RSV2': ' ', 'Y1RSV3': ' '}

    b = {'Y1DTTM': '2021060208150053400020', 'Y1DTTP': 3, 'Y1MP': 'EA', 'Y1SQII': 2929302, 'Y1SQID': 1, 'Y1FQII': 550560,
     'Y1FQID': 5925, 'Y1FQPK': 1, 'Y1ISTY': 'CZ3', 'Y1SECP': 20001, 'Y1ACDE': 'QFV12', 'Y1QISI': 3, 'Y1BOXI': 24,
     'Y1BOXF': 24, 'Y1QISF': 24, 'Y1USED': 20210602, 'Y1USET': 84500, 'Y1SETI': 'AAG01', 'Y1XISS': '11L01', 'Y1INPN': 3,
     'Y1STKN': 'OC', 'Y1XLOC': 'S800011', 'Y1IT': 'J0252851', 'Y1SD': 'D', 'Y1SP': ' ', 'Y1PS': 'P',
     'Y1ITOH': 'J0252851D-+-P', 'Y1PCK': 0.0, 'Y1PCKQ': 0.0, 'Y1NOOV': ' ', 'Y1XTIV': 1, 'Y1CON': ' ', 'Y1PLT': ' ',
     'Y1TRD': 20210602, 'Y1TRT': 83500, 'Y1FLAG': ' ', 'Y1XOPA': ' ', 'Y1XOPB': ' ', 'Y1XOPC':'', 'Y1XOPD': ' ',
     'Y1XOPE': ' ', 'Y1XOPF': ' ', 'Y1XOPG': ' ', 'Y1RSV1': ' ', 'Y1RSV2': ' ', 'Y1RSV3': ' '}

    c = [('202106020815005340001', 4, 'EA', 9909004, 1, 550553, 307, 1, 'VM1', 1113, '12#4F', 83, 2000, 2000, 800, 20210602,
      84500, 'AAUTO', 'AAUTO', 1, 'OC', 'TC00001', 'D2625368', 'A', '', 'P', 'D2625368A-+-P', 11000001.0, 400.0, '',
      1.0, 'TRUCK1', 'TEST0001', 20210602, 81500, '', '', '', '', '', '*', '', '', '', '', '')]

    d = '2021060208150053400020'

    OCR_table_obj = OCR_DATA(conn.user_name, conn.password, conn.db_name, host=conn.location)
    dict_, list_ = OCR_table_obj.read_excel()

    # print(list_)
    OCR_table_obj.update_ocr("Y1MP", "10", d)




